package veterinaria.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import veterinaria.excepcion.DAOExcepcion;
import veterinaria.modelo.Doctor;
import veterinaria.modelo.Persona;
import veterinaria.util.ConexionBD;

public class DoctorDAO extends BaseDAO {

	public Collection<Doctor> buscarPorNombre(String pNom) throws DAOExcepcion {
		String sqlDoc="Select a.DocCodigo,a.DocAdmin,a.UsuCodigo,a.MPersona_PerCodigo,b.PerNombre,b.PerDomicilio,b.PerTelefono,b.PerCelular,b.PerCorreo,b.PerImagen From MDoctor a, MPersona b Where a.MPersona_PerCodigo=b.PerCodigo And b.PerNombre Like ? And a.DocFlg<>'E' Order By a.DocCodigo";
		Collection<Doctor> lisDoc=new ArrayList<Doctor>();
		Connection conDoc=null;
		PreparedStatement comDoc=null;
		ResultSet cRst=null;
		try {
			conDoc=ConexionBD.obtenerConexion();
			comDoc=conDoc.prepareStatement(sqlDoc);
			comDoc.setString(1, "%" + pNom + "%");
			cRst=comDoc.executeQuery();
			while (cRst.next()) {
				Doctor cDoc=new Doctor();
				Persona cPer=new Persona();
				cPer.setPerCodigo(cRst.getInt("MPersona_PerCodigo"));
				cPer.setPerNombre(cRst.getString("PerNombre"));
				cPer.setPerDomicilio(cRst.getString("PerDomicilio"));
				cPer.setPerTelefono(cRst.getString("PerTelefono"));
				cPer.setPerCelular(cRst.getString("PerCelular"));
				cPer.setPerCorreo(cRst.getString("PerCorreo"));
				cPer.setPerImagen(cRst.getString("PerImagen"));				
				cDoc.setDocCodigo(cRst.getInt("DocCodigo"));
				cDoc.setDocAdmin(cRst.getString("DocAdmin"));
				cDoc.setUsuCodigo(cRst.getString("UsuCodigo"));
				cDoc.setPersona(cPer);
				lisDoc.add(cDoc);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
			
		} finally {
			this.cerrarResultSet(cRst);
			this.cerrarStatement(comDoc);
			this.cerrarConexion(conDoc);
		}
		System.out.println(lisDoc.size());
		return lisDoc;
	}

	public Doctor insertar(Doctor pReg) throws DAOExcepcion {
		String cSql = "Insert Into MDoctor(DocAdmin,UsuCodigo,DocFlg,MPersona_PerCodigo) Values (?,?,?,?)";
		Connection cCon = null;
		PreparedStatement cCom = null;
		ResultSet cRst = null;
		try {
			cCon = ConexionBD.obtenerConexion();
			cCom = cCon.prepareStatement(cSql);
			cCom.setString(1, pReg.getDocAdmin());
            cCom.setString(2, pReg.getUsuCodigo());
            cCom.setString(3, "A");
            cCom.setInt(4, pReg.getPersona().getPerCodigo());			
			int nroErr = cCom.executeUpdate();
			if (nroErr != 1) {
				throw new SQLException("No se pudo insertar");
			}
			int newCod = 0;
			cSql="Select Last_Insert_id()";
			cCom=cCon.prepareStatement(cSql);
			cRst=cCom.executeQuery();
			if (cRst.next()) {
				newCod=cRst.getInt(1);
			}
			pReg.setDocCodigo(newCod);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(cRst);
			this.cerrarStatement(cCom);
			this.cerrarConexion(cCon);
		}
		return pReg;
	}

	public Doctor obtener(int pCod) throws DAOExcepcion {
		Doctor cReg = new Doctor();
		Connection cCon = null;
		PreparedStatement cCom = null;
		ResultSet cRst = null;
		try {
			String query = "Select DocCodigo, DocAdmin, UsuCodigo From MDoctor Where DocCodigo=? And DocFlg<>'E'";
			cCon=ConexionBD.obtenerConexion();
			cCom=cCon.prepareStatement(query);
			cCom.setInt(1, pCod);
			cRst=cCom.executeQuery();
			if (cRst.next()) {
				cReg.setDocCodigo(cRst.getInt(1));
				cReg.setDocAdmin(cRst.getString(2));
				cReg.setUsuCodigo(cRst.getString(3));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(cRst);
			this.cerrarStatement(cCom);
			this.cerrarConexion(cCon);
		}
		return cReg;
	}

	public void eliminar(int pCod) throws DAOExcepcion {
		String cSql="UpDate MDoctor Set DocFlg=? Where DocCodigo=? And DocFlg<>'E'";
		Connection cCon=null;
		PreparedStatement cCom=null;
		try {
			cCon=ConexionBD.obtenerConexion();
			cCom=cCon.prepareStatement(cSql);
			cCom.setString(1, "E");
			cCom.setInt(2, pCod);
			int i = cCom.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se encontro el registro");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(cCom);
			this.cerrarConexion(cCon);
		}
	}

	public Doctor actualizar(Doctor pReg) throws DAOExcepcion {
		String cSql="UpDate MDoctor Set DocAdmin=?,UsuCodigo=?,DocFlg=?,MPersona_PerCodigo=? Where DocCodigo=? And DocFlg<>'E'";
		Connection cCon=null;
		PreparedStatement cCom=null;
		try {
			cCon=ConexionBD.obtenerConexion();
			cCom=cCon.prepareStatement(cSql);
			cCom.setString(1, pReg.getDocAdmin());
			cCom.setString(2, pReg.getUsuCodigo());
			cCom.setString(3, "M");
			cCom.setInt(4, pReg.getPersona().getPerCodigo());
			cCom.setInt(5, pReg.getDocCodigo());			
			int i = cCom.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(cCom);
			this.cerrarConexion(cCon);
		}
		return pReg;
	}
	
	public String busUsuario(String pUsu) throws DAOExcepcion {
		Doctor cReg = new Doctor();
		Connection cCon = null;
		PreparedStatement cCom = null;
		ResultSet cRst = null;
		try {
			String query = "Select DocCodigo, DocAdmin, UsuCodigo From MDoctor Where UsuCodigo=? And DocFlg<>'E'";
			cCon=ConexionBD.obtenerConexion();
			cCom=cCon.prepareStatement(query);
			cCom.setString(1, pUsu);
			cRst=cCom.executeQuery();
			if (cRst.next()) {
				cReg.setDocCodigo(cRst.getInt(1));
				cReg.setDocAdmin(cRst.getString(2));
				cReg.setUsuCodigo(cRst.getString(3));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(cRst);
			this.cerrarStatement(cCom);
			this.cerrarConexion(cCon);
		}
		return cReg.getUsuCodigo();
	}
	
	public Collection<Doctor> listar() throws DAOExcepcion {
		Collection<Doctor> c = new ArrayList<Doctor>();
		Connection cCon=null;
		PreparedStatement cCom=null;
		ResultSet cRst=null;
		try {
			cCon = ConexionBD.obtenerConexion();
			String cSql="Select a.DocCodigo,a.DocAdmin,a.UsuCodigo,a.MPersona_PerCodigo,b.PerNombre,b.PerDomicilio,b.PerTelefono,b.PerCelular,b.PerCorreo,b.PerImagen From MDoctor a, MPersona b Where a.MPersona_PerCodigo=b.PerCodigo And a.DocFlg<>'E' Order By a.DocCodigo";
			cCom=cCon.prepareStatement(cSql);
			cRst=cCom.executeQuery();
			while (cRst.next()) {
				Doctor cDoc=new Doctor();
				Persona cPer=new Persona();
				cPer.setPerCodigo(cRst.getInt("MPersona_PerCodigo"));
				cPer.setPerNombre(cRst.getString("PerNombre"));
				cPer.setPerDomicilio(cRst.getString("PerDomicilio"));
				cPer.setPerTelefono(cRst.getString("PerTelefono"));
				cPer.setPerCelular(cRst.getString("PerCelular"));
				cPer.setPerCorreo(cRst.getString("PerCorreo"));
				cPer.setPerImagen(cRst.getString("PerImagen"));				
				cDoc.setDocCodigo(cRst.getInt("DocCodigo"));
				cDoc.setDocAdmin(cRst.getString("DocAdmin"));
				cDoc.setUsuCodigo(cRst.getString("UsuCodigo"));
				cDoc.setPersona(cPer);
				c.add(cDoc);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(cRst);
			this.cerrarStatement(cCom);
			this.cerrarConexion(cCon);
		}
		return c;
	}
}
